# -*- coding:utf-8 -*-
import threading
import time
from threading import Lock, Event
import paramiko
import pymysql
import redis

# r = redis.Redis(host="10.33.64.99", port=6379, db=1)
# conn = pymysql.connect(host="10.33.64.99", user="root", passwd="vdin1234", db="tsgz", charset="utf8")
r = redis.Redis(host="127.0.0.1", port=6379, db=1)
conn = pymysql.connect(host="127.0.0.1", user="root", passwd="1213", db="tsgz_test", charset="utf8")

cursor = conn.cursor()


class SysSSHClient(object):
    def __init__(self, hostname, username, password, port=22, keepalive=None):
        self.hostname = hostname
        self.username = username
        self.password = password
        self.port = port
        self.keepalive = keepalive
        self.connected = Event()
        self.lock = Lock()
        self.ssh = paramiko.SSHClient()
        self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        self._connect()

    def _do_connect(self):
        self.ssh.connect(hostname=self.hostname, username=self.username, password=self.password, port=self.port)

    def _connect(self):
        """ Connect to the remote if not already connected. """
        if not self.connected.is_set():
            try:
                self.lock.acquire()
                # Another thread may have connected while we were
                # waiting to acquire the lock
                if not self.connected.is_set():
                    self._do_connect()
                    self.connected.set()
            except :
                raise
            finally:
                self.lock.release()

    def run_command(self, command):

        stdin, stdout, stderr = self.ssh.exec_command(command)
        return stdout

    def close(self):
        self.ssh.close()

ssh = SysSSHClient(hostname='10.33.64.99', username='root', password='Vdin1234', port=22)
time_list = [str(i)+':00:00' if i >= 10 else '0'+str(i)+':00:00' for i in range(24)]
oids = ['ifOutOctets.777', 'ifInOctets.777', 'ifOutOctets.1025', 'ifInOctets.1025']


def redis_handler(time_str):
    print "Redis_hanler Start....."

    while True:
        # print "Redis_handler:", t1
        if not time_str == time.strftime("%m%d"):
            r.expire("fw_tx_" + time_str, 24 * 60 * 60)
            r.expire("fw_rx_" + time_str, 24 * 60 * 60)
            r.expire("sw_tx_" + time_str, 24 * 60 * 60)
            r.expire("sw_rx_" + time_str, 24 * 60 * 60)
            r.expire("delta-t" + time_str, 24 * 60 * 60)
            time_str = time.strftime("%m%d")
        cmd = ''
        t0 = time.time()
        time.sleep(0.82)
        for oid in oids:
            cmd += 'snmpwalk -v 2c -c Vdin1234 1.1.1.1 %s && ' % oid
        result = ssh.run_command(cmd.strip('&& ')).readlines()
        delta_t = time.time() - t0
        print delta_t, result
        # results.append(result.strip().split()[-1])
        r.rpush("delta_t_" + time_str, delta_t)
        r.rpush("fw_tx_" + time_str, result[0].strip().split()[-1])
        r.rpush("fw_rx_" + time_str, result[1].strip().split()[-1])
        r.rpush("sw_tx_" + time_str, result[2].strip().split()[-1])
        r.rpush("sw_rx_" + time_str, result[3].strip().split()[-1])
        # print results


def delta_handle(a, b):
    if a < b:
        return 4294967296 - b + a
    else:
        return a - b


def sql_handler(time_str, delta_t):
    ft0 = r.lrange("fw_tx_" + time_str, -1, -1)[0]
    fr0 = r.lrange("fw_rx_" + time_str, -1, -1)[0]
    sr0 = r.lrange("sw_rx_" + time_str, -1, -1)[0]
    st0 = r.lrange("sw_tx_" + time_str, -1, -1)[0]
    while True:
        t = time.time()
        print "ftrsrt", ft0, fr0, sr0, st0
        time.sleep(delta_t)
        if not time_str == time.strftime("%m%d"):
            time_str = time.strftime("%m%d")
        ft1 = r.lrange("fw_tx_" + time_str, -1, -1)[0]
        fr1 = r.lrange("fw_rx_" + time_str, -1, -1)[0]
        sr1 = r.lrange("sw_rx_" + time_str, -1, -1)[0]
        st1 = r.lrange("sw_tx_" + time_str, -1, -1)[0]
        d_ft = delta_handle(int(ft1), int(ft0))
        d_fr = delta_handle(int(fr1), int(fr0))
        d_sr = delta_handle(int(sr1), int(sr0))
        d_st = delta_handle(int(st1), int(st0))
        print "Delta-T:", d_ft, d_fr, d_st, d_sr
        print ft0, fr0, sr0, st0, ft1, fr1, sr1, st1
        ft0, fr0, sr0, st0 = ft1, fr1, sr1, st1
        sql = """INSERT INTO app_timeslotnetflow(firewall_tx, firewall_rx, switch_tx, switch_rx, 
                          start_time, end_time) VALUES (%s, %s, %s, %s, %s, %s)"""
        try:
            cursor.execute(sql, (str(d_ft), str(d_fr), str(d_st), str(d_sr),
                                 time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t - delta_t)),
                                 time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))))
            conn.commit()
        except Exception as e:
            print e


if __name__ == "__main__":
    event = threading.Event()
    time_str = time.strftime("%m%d")
    # redis_handler(time_str)
    t1 = threading.Thread(target=redis_handler, args=(time_str,))
    t1.setDaemon(True)
    t1.start()
    time.sleep(10)
    while True:
        if int(time.strftime("%M")) % 5 == 0:
            # t2 = threading.Thread(target=sql_handler2, args=(time_str, 10))
            # t2.start()
            sql_handler(time_str, 300)
            break